package packageGenerateData;

import packageInputData.DbConnection;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

import routine.FunctionLib;

public class GenerateDataSXYZ {

	/**
	 * function 
	 * to generate SXYZ data and put it into the database
	 *  @return			void
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generateSXYZ()
	{
		
		List list1 = new ArrayList();
		List list2 = new ArrayList();
		List list3 = new ArrayList();		
		
		String req="";
		int 	inserts=0,epok=0,
				igsID=0,igsTimeID;		
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
		QueryRunner qRunner = new QueryRunner();
		//get data from table "param"
		Date paramDate= 	(Date)(DbConnection.getFromDB("date", "param"));
		int paramDoy= 		(Integer)(DbConnection.getFromDB("doy", "param"));		
		int paramYear=		(paramDate.getYear())+1900;		
		double dategps []=new double[2];
		
		dategps=FunctionLib.dategps(paramYear, paramDoy);
		
		//get id  from igs database
		//execeute query
		try {
			req="select id from igs where week="+(int)dategps[0]+" and diw="+(int)dategps[1];
			list1 =(List) qRunner.query(conn,
						    		   req,
						    		   new ArrayListHandler());						
		} catch (SQLException e2) {
	       e2.printStackTrace();	
		}
		//get the result
		if(list1.size()>0)
		{
			Object data[] = (Object[]) list1.get(0);					
			igsID=(Integer)data[0];
			
			for(int sat=1;sat<=31;sat++)
			{
				//get id,timestamp  from igs_times database
				//execute query
				try {
					req="select id,tmstamp from igs_times where id_igs="+igsID;
					list2 =(List) qRunner.query(conn,
								    		   req,
								    		   new ArrayListHandler());
										
				} catch (SQLException e2) {
			       e2.printStackTrace();		
				}
				//get the result
				if(list2.size()>0)
				{
					for(int i=0;i<list2.size();i++)
					{
						Object data2[] = (Object[]) list2.get(i);
						igsTimeID=(Integer)data2[0];
						Timestamp time=(Timestamp)data2[1];				
						epok=(time.getMinutes())*2+(time.getHours())*120;
						
						//get coordinates from igs_data  from igs_data database
						try {
							req="select xsat,ysat,zsat from igs_data where sat="+sat+" and id_igs_times= "+igsTimeID;
							list3 =(List) qRunner.query(conn,
										    		   req,
										    		   new ArrayListHandler());						
						} catch (SQLException e2) {
					       e2.printStackTrace();			
						}
						
						if(list3.size()>0)
						{
							Object data3[] = (Object[]) list3.get(0);					
							//prepare query
							req="INSERT INTO sxyz" +
									"(session_id,epok,xsat,ysat,zsat,sat,doy)" +				
									"VALUES (?,?,?,?,?,?,?)";
							//execute query
							 try {
									inserts = qRunner.update(conn,req,
												MainClass.session,
												epok,
												(Double)data3[0],
												(Double)data3[1],
												(Double)data3[2],
												sat,
												paramDoy);
							 } catch (SQLException e) {									
									e.printStackTrace();
							 }
						}
					list3.clear();
					}
				}			
				list2.clear();
				//test if insertion is done properly
				if (inserts==1)
				{
	  				System.out.println("\tInsertion in sxyz for sat "+sat +" done ");
	  				inserts=0;
				}
			}
		}		
		list1.clear();
		
		//close connection
		DbUtils.closeQuietly(conn);
	}
}
